﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CategoriesRetriever
{
    class Program
    {
        static void RetrieveProducts()
        {
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;User ID=Ivan;Password=1234");
            conn.Open();
            using (conn)
            {
                SqlCommand cmd = new SqlCommand("select Products.ProductName,Categories.CategoryName from Categories" +
                 " join Products on Products.CategoryID = Categories.CategoryID", conn);
                SqlDataReader reader = cmd.ExecuteReader();

                using (reader)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["CategoryName"] + " " + reader["ProductName"]);
                    }
                }
            }
        }

        static void AddProduct(string productName, byte discontinued)
        {
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;User ID=Ivan;Password=1234");
            conn.Open();
            using (conn)
            {
                SqlCommand cmd = new SqlCommand("insert into Products (ProductName,Discontinued) values "
                    +"(@productName,@discontinued)", conn);
            
                cmd.Parameters.Add(new SqlParameter("@productName", productName));
               // byte d = 0;
                cmd.Parameters.Add(new SqlParameter("@discontinued", discontinued ));
                
                if (cmd.ExecuteNonQuery() > 0)
                {
                    Console.WriteLine("Record added!");
                }
                else
                {
                    Console.WriteLine("FAIL!");
                }
                 
            }
        }

        static void Main(string[] args)
        {
            //RetrieveProducts();
            //AddProduct("Pear",0);

        }
    }
}
